package com.mvc.business.service;

import java.io.Serializable;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.hibernate.engine.SessionFactoryImplementor;
import org.hibernate.impl.CriteriaImpl;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.stereotype.Service;

import com.mvc.base.model.Picture;
import com.mvc.base.service.BaseCompanyManager;
import com.mvc.base.service.CompanyManager;
import com.mvc.base.service.PictureManager;
import com.mvc.base.service.SupplierManager;
import com.mvc.base.service.UserCompanyManager;
import com.mvc.business.model.Order;
import com.mvc.business.model.Purchase;
import com.mvc.business.model.Schedule;
import com.mvc.finance.model.FinancePrivilege;
import com.mvc.finance.service.FinancePrivilegeManager;
import com.mvc.framework.dao.HibernateIdUtils;
import com.mvc.framework.dao.HibernateWebUtils;
import com.mvc.framework.model.Auditable;
import com.mvc.framework.model.CommonAuditableEntity;
import com.mvc.framework.util.FrameworkContextUtils;
import com.mvc.framework.util.PageSearch;

@Service
public class PurchaseManager extends BaseCompanyManager<Purchase, Integer> {
	@Autowired
	private PurchaseItemManager	    purchaseItemManager;

	@Autowired
	private SupplierManager	        supplierManager;

	@Autowired
	private OrderManager	        orderManager;

	@Autowired
	private CompanyManager	        companyManager;

	@Autowired
	private PictureManager	        pictureManager;

	@Autowired
	private UserCompanyManager	    userCompanyManager;

	@Autowired
	private FinancePrivilegeManager	financePrivilegeManager;

	public Purchase get(Serializable id) {
		Purchase purchase = super.get(id);
		if (purchase != null) {
			purchase.setSupplier(supplierManager.getByObjectId(purchase.getSupplierId()));
			purchase.setCompany(companyManager.getByObjectId(purchase.getCompanyId()));
			purchase.setPictures(pictureManager.getPicturesByItemIdAndType(purchase.getObjectId(), Picture.PICTURE_OF_PURCHASE));
		}

		return purchase;
	}

	public Purchase getObject(Integer objectId) {
		Purchase order = super.getByObjectId(objectId);
		if (order != null) {
			order.setPurchaseItems(purchaseItemManager.getPurchaseItemsByPurchaseId(objectId));
			order.setOrder(orderManager.getByObjectId(order.getOrderId()));
			order.setCompany(companyManager.getByObjectId(order.getCompanyId()));
			order.setSupplier(supplierManager.getByObjectId(order.getSupplierId()));
		}
		return order;
	}

	public Long getNeedAuditCount() {
		Session session = getHibernateTemplate().getSessionFactory().openSession();
		Query query = session.createQuery("select count(*) from " + Purchase.class.getName() + " where recStatus = ? and status = ?");
		query.setParameter(0, Auditable.REC_STATUS_VALID).setParameter(1, Order.ORDER_STATUS_COMMIT);
		List<Object> result = query.list();
		session.close();
		return (Long) result.get(0);
	}

	public void save(Purchase entity) {
		entity.setTotal(purchaseItemManager.getTotal(entity.getObjectId()));
		if (null == entity.getId()) {
			Integer oldOrderId = entity.getObjectId();
			entity.setObjectId(HibernateIdUtils.getUnCacheCustomizeId(getHibernateTemplate().getSessionFactory(), "purchaseNo"));
			purchaseItemManager.updatePurchaseId(oldOrderId, entity.getObjectId());
			pictureManager.updatePurchaseId(oldOrderId, entity.getObjectId());
		}
		if (entity.getType() == Purchase.PURCHASE_TYPE_ORDER) {
			entity.setCompanyId(orderManager.getByObjectId(entity.getOrderId()).getCompanyId());
		}
		super.save(entity);
	}

	public void savePay(int purchaseId) {
		Purchase purchase = getByObjectId(purchaseId);
		if (purchase != null && purchase.getFinanceStatus() == Purchase.FINANCE_STATUS_UNPAY) {
			purchase.setFinanceStatus(Purchase.FINANCE_STATUS_PAYED);
			save(purchase);
		}
	}

	public List<Purchase> getUnPayPurchases(Integer supplierId) {
		String sql = "select A from " + Purchase.class.getName()
		        + " A where A.recStatus=? and A.status>=? and A.financeStatus=? and supplierId = ?";

		return searchBySql(sql, new Object[] { CommonAuditableEntity.REC_STATUS_VALID, Purchase.STATUS_APPROVED,
		        Purchase.FINANCE_STATUS_UNPAY, supplierId });

	}

	public List<Purchase> getUnPayPurchases() {
		String sql = "select A from " + Purchase.class.getName() + " A where A.recStatus=? and A.status>=? and A.financeStatus=?";
		String companyId = financePrivilegeManager.getCompanyIdsByUserId(FrameworkContextUtils.getCurrentUserId(),
		        FinancePrivilege.TYPE_DUES);
		if (companyId == null) {
			return searchBySql(sql, new Object[] { CommonAuditableEntity.REC_STATUS_VALID, Purchase.STATUS_APPROVED,
			        Purchase.FINANCE_STATUS_UNPAY });
		} else if (companyId.length() > 0) {
			sql += " and companyId in(" + companyId + ")";
			return searchBySql(sql, new Object[] { CommonAuditableEntity.REC_STATUS_VALID, Order.ORDER_STATUS_APPROVED,
			        Order.ORDER_FINANCE_STATUS_UNPAY });
		} else {
			return new ArrayList<Purchase>();
		}
	}

	public List<Purchase> getNeedPayPurchase(Date noticeDate) {
		StringBuilder querySql = new StringBuilder(200);
		querySql.append("select A from ").append(Purchase.class.getName()).append(" A where A.financeStatus != ?")
		        .append(" AND A.predictPayDate<? AND A.recStatus = ? AND A.status >= ?").append(" AND A.status < ?");
		return searchBySql(querySql.toString(), new Object[] { Purchase.FINANCE_STATUS_PAYED, noticeDate, Auditable.REC_STATUS_VALID,
		        Purchase.STATUS_APPROVED, Purchase.STATUS_FINISHED });
	}

	public List<Purchase> getAllPurchase() {
		StringBuilder querySql = new StringBuilder(200);
		querySql.append("select A from ").append(Purchase.class.getName()).append(" A where  A.recStatus = ? AND A.status >= ?")
		        .append(" AND A.status <= ?");
		return searchBySql(querySql.toString(), new Object[] { Auditable.REC_STATUS_VALID, Purchase.STATUS_APPROVED,
		        Purchase.STATUS_FINISHED });
	}

	public List<Purchase> getNeedNoticePurchase(Date noticeDate) {
		StringBuilder querySql = new StringBuilder(200);
		querySql.append("select A from ").append(Purchase.class.getName()).append(" A where A.objectId in(")
		        .append("select distinct purchaseId from ").append(Schedule.class.getName())
		        .append(" B where B.closeDate<? AND B.recStatus = ? AND B.finished=?) AND  A.recStatus = ? ");
		return searchBySql(querySql.toString(), new Object[] { noticeDate, Order.REC_STATUS_VALID, Boolean.FALSE, Order.REC_STATUS_VALID });
	}

	public void financeFind(final PageSearch page) {
		final Integer userId = FrameworkContextUtils.getCurrentUserId();
		getHibernateTemplate().execute(new HibernateCallback<PageSearch>() {
			public PageSearch doInHibernate(Session session) {
				Criteria criteria = HibernateWebUtils.createCriteria(session, page.getEntityClass(), page.getFilters());
				if (userId != null) {
					criteria.add(Restrictions.sqlRestriction("company_Id in (select B.company_Id from B_FINANCE_PRIVILEGE B"
					        + " where B.user_id = " + userId + " and B.type= " + FinancePrivilege.TYPE_DUES + ")"));
				}
				if (0 == page.getTotalCount()) {
					criteria.setProjection(Projections.rowCount());
					Object object = criteria.uniqueResult();
					int total = object == null ? 0 : ((Number) object).intValue();
					page.setTotalCount(total);
				}
				addOrder(page, criteria);
				criteria.setProjection(null);
				List items = criteria.setFirstResult(page.getBegin()).setMaxResults(page.getPageSize()).list();
				page.setList(items);
				return page;
			}
		});
	}

	public void find(final PageSearch page) {
		final Integer userId = FrameworkContextUtils.getCurrentUserId();
		getHibernateTemplate().execute(new HibernateCallback<PageSearch>() {
			public PageSearch doInHibernate(Session session) {
				CriteriaImpl criteria = (CriteriaImpl) session.createCriteria(page.getEntityClass());
				String condition = HibernateWebUtils.buildHql(page.getFilters(), criteria,
				        (SessionFactoryImplementor) session.getSessionFactory());
				String sql = " FROM B_purchase this_,B_order B where this_.order_id = B.object_Id AND B.rec_Status = 1 ";
				if (StringUtils.isNotBlank(condition)) {
					sql += condition;
				}
				if (page.getFilterValue("EQI_supplierId") == null) {
					if (userId != null) {
						sql += (" and this_.company_Id in (select C.company_Id from B_USER_COMPANY C" + " where C.user_id = " + userId + ")");
					}
				}

				Object[] params = HibernateWebUtils.getQueryParameter(page.getFilters());
				if (0 == page.getTotalCount()) {
					String countSql = "SELECT COUNT(*) " + sql;
					SQLQuery query = session.createSQLQuery(countSql);
					HibernateWebUtils.setQueryParameter(query, params);
					List<Long> count = query.list();
					int total = count == null ? 0 : ((Number) count.get(0)).intValue();
					page.setTotalCount(total);
				}
				if (page.getSortProperty() == null) {
					sql += " ORDER BY object_id desc";
				} else {
					sql += " ORDER BY this_." + HibernateWebUtils.convertCamelStyleToUpperCase(page.getSortProperty()) + " "
					        + page.getSortOrder();
				}
				SQLQuery query = session.createSQLQuery("SELECT this_.*,B.* " + sql);
				HibernateWebUtils.setQueryParameter(query, params);
				query.addEntity("this_", Purchase.class);
				query.addEntity("B", Order.class);
				List items = query.setFirstResult(page.getBegin()).setMaxResults(page.getPageSize()).list();
				List<Purchase> list = new ArrayList<Purchase>();
				for (Object[] objects : (List<Object[]>) items) {
					Purchase order = (Purchase) objects[0];
					order.setOrder((Order) objects[1]);
					list.add(order);
				}
				page.setList(list);
				return page;
			}
		});

	}

	public void findForBankStatementImport(final PageSearch page) {
		final Integer userId = FrameworkContextUtils.getCurrentUserId();
		getHibernateTemplate().execute(new HibernateCallback<PageSearch>() {
			public PageSearch doInHibernate(Session session) {
				CriteriaImpl criteria = (CriteriaImpl) session.createCriteria(page.getEntityClass());
				String condition = HibernateWebUtils.buildHql(page.getFilters(), criteria,
				        (SessionFactoryImplementor) session.getSessionFactory());
				String sql = " FROM B_purchase this_,B_order B where this_.order_id = B.object_Id AND B.rec_Status = 1 ";
				if (StringUtils.isNotBlank(condition)) {
					sql += condition;
				}
				if (page.getFilterValue("EQI_supplierId") == null) {
					if (userId != null) {
						sql += (" and this_.company_Id in (select C.company_Id from B_USER_COMPANY C" + " where C.user_id = " + userId + ")");
					}
				}
				sql += " and exists (select 1 from b_purchase_item a where a.rec_status = 1 and a.purchase_id >0 and a.purchase_id = this_.object_id and (not exists ("
				        + " select 1 from b_bank_statement_item c where c.rec_status = 1 and c.bank_statement_id >0 and c.purchase_item_id = a.object_id ) "
				        + " or exists (select 1 from b_bank_statement_item c where c.rec_status = 1 and c.bank_statement_id >0 and c.purchase_item_id = a.object_id and c.quantity < a.quantity)))";

				Object[] params = HibernateWebUtils.getQueryParameter(page.getFilters());
				if (0 == page.getTotalCount()) {
					String countSql = "SELECT COUNT(*) " + sql;
					SQLQuery query = session.createSQLQuery(countSql);
					HibernateWebUtils.setQueryParameter(query, params);
					List<Long> count = query.list();
					int total = count == null ? 0 : ((Number) count.get(0)).intValue();
					page.setTotalCount(total);
				}
				if (page.getSortProperty() == null) {
					sql += " ORDER BY object_id desc";
				} else {
					sql += " ORDER BY this_." + HibernateWebUtils.convertCamelStyleToUpperCase(page.getSortProperty()) + " "
					        + page.getSortOrder();
				}
				SQLQuery query = session.createSQLQuery("SELECT this_.*,B.* " + sql);
				HibernateWebUtils.setQueryParameter(query, params);
				query.addEntity("this_", Purchase.class);
				query.addEntity("B", Order.class);
				List items = query.setFirstResult(page.getBegin()).setMaxResults(page.getPageSize()).list();
				List<Purchase> list = new ArrayList<Purchase>();
				for (Object[] objects : (List<Object[]>) items) {
					Purchase order = (Purchase) objects[0];
					order.setOrder((Order) objects[1]);
					list.add(order);
				}
				page.setList(list);
				return page;
			}
		});

	}

	public List<Object> getCount(Integer companyId, Date beginDate, Date endDate) {
		Session session = getHibernateTemplate().getSessionFactory().openSession();
		Query query = session.createQuery("select count(*) from " + Purchase.class.getName()
		        + " where recStatus = ? and status>= ? and companyId=? and auditDate >= ? and auditDate <= ?");
		query.setParameter(0, Auditable.REC_STATUS_VALID).setParameter(1, Order.ORDER_STATUS_APPROVED).setParameter(2, companyId)
		        .setParameter(3, beginDate).setParameter(4, endDate);
		List<Object> result = query.list();
		session.close();
		return result;
	}

	public List<Object[]> summary(Integer companyId, Date beginDate, Date endDate) {
		Session session = getHibernateTemplate().getSessionFactory().openSession();
		Query query = session.createQuery("select currency,sum(total) from " + Purchase.class.getName()
		        + " where recStatus = ? and status>= ? and companyId=? and auditDate >= ? and auditDate <= ? group by currency");
		query.setParameter(0, Auditable.REC_STATUS_VALID).setParameter(1, Order.ORDER_STATUS_APPROVED).setParameter(2, companyId)
		        .setParameter(3, beginDate).setParameter(4, endDate);
		List<Object[]> result = query.list();
		session.close();
		return result;
	}
}
